package com.h3c.nem.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.h3c.nem.entity.TbOfferWorker;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 预备员工基本信息数据库访问层
 *
 * @author chenyifan
 * @since 2022-06-02 14:19:48
 */
public interface TbOfferWorkerMapper extends BaseMapper<TbOfferWorker> {

    @Insert("INSERT INTO tb_offer_worker \n" +
            "(worker_id, regular_worker_id, domain_account, worker_name, name_pinyin, first_pinyin, last_pinyin, \n" +
            "old_name, cert_type, cert_no, birthday, phone_no, email, sex, \n" +
            "contact_address, political_landscape, add_party_date, nationality, nation_place, nationality_code, \n" +
            "house_hold_city, house_hold_code, nation, first_work_date, archive_org, project_worker_flag, \n" +
            "personnel_range_code, personnel_range, spouse_flag, spouse_name, marriage_date, \n" +
            "spouse_phone, spouse_address, spouse_org, work_city, outer_city, insured_city, foreign_residency, foreign_residency_country_code, \n" +
            "dept1name, dept2name, dept3name, dept4name, dept_name_chain,need_sign_competition, competition_sign_flag, \n" +
            "competition_period, competition_company, competition_sign_date, competition_reward_standard, data_source, \n" +
            "in_type, last_leave_date, probation_month, apply_report_date, plan_report_date, offer_date, join_date, preparation_password,\n" +
            "offer_id, worker_status, offer_status, update_time, updator_info) \n" +
            "SELECT \n" +
            "d.HC_NO,d.EmpCode,d.DomainAccount,d.name,CONCAT(d.FirstPinyin,d.LastPinyin), CASE WHEN LENGTH(d.FirstPinyin) > 16 THEN '' ELSE d.FirstPinyin END,d.LastPinyin,\n" +
            "d.OldName,'1',d.CARD_ID, CASE WHEN d.Birthday='' THEN NULL WHEN length(d.Birthday) != 10 THEN NULL WHEN d.Birthday LIKE '%.%' THEN NULL ELSE STR_TO_DATE(d.Birthday,'%Y-%m-%d') END, CASE WHEN length(LTRIM(d.telPhone)) != 11 THEN '' WHEN d.telPhone is NULL THEN '' ELSE d.telPhone END, d.EmpEmail,d.Sex,\n" +
            "d.ContactAddress,d.PoliticalLandscape,CASE WHEN d.AddPartTime='' THEN NULL WHEN length(d.AddPartTime) != 10 THEN NULL WHEN d.AddPartTime LIKE '%.%' THEN NULL ELSE STR_TO_DATE(d.AddPartTime,'%Y-%m-%d') END,d.Nationality,d.NationPlace,d.NationalityCode,\n" +
            "d.HouseHoldCity,d.HouseHoldCode,d.Nation,\n" +
            "CASE WHEN d.SocialService='' THEN NULL WHEN length(d.SocialService) != 10 THEN NULL WHEN d.SocialService LIKE '%.%' THEN NULL ELSE STR_TO_DATE(d.SocialService,'%Y-%m-%d') END,\n" +
            "d.Archives,d.IsProjectSystemEmp,\n" +
            "c.PersonnelRangeCode, d.PersonnelRange, d.IsHaveSpouse, d.SpouseName,CASE WHEN d.MarriedTime='' THEN NULL WHEN length(d.MarriedTime) != 10 THEN NULL WHEN d.MarriedTime LIKE '%.%' THEN NULL ELSE STR_TO_DATE(d.MarriedTime,'%Y-%m-%d') END,\n" +
            "d.SpouseTel,d.ContactAddress,d.NowCompany, c.OfficialHousing, c.ExpatriateHousing, c.InsuranceArea,d.ForeignResidency,#{str},\n" +
            "d.Dept1Name,d.Dept2Name,d.Dept3Name,'',\n" +
            "CONCAT(d.Dept1Name,CASE WHEN d.Dept2Name='' THEN '' ELSE CONCAT('/',d.Dept2Name) END, CASE WHEN d.Dept3Name='' THEN '' ELSE CONCAT('/',d.Dept3Name) END),d.IsSignAgreement,d.IsSigned,\n" +
            "d.SignedLimit,d.SignedList,\n" +
            "CASE WHEN d.SignedTime='' THEN NULL ELSE STR_TO_DATE(SUBSTR(d.SignedTime,1,INSTR(d.SignedTime,' ')-1),'%Y-%m-%d') END,\n" +
            "d.SignedStrand,CASE WHEN d.Send_Person='beisen'THEN '北森' ELSE 'BPM' END,\n" +
            "d.InType,CASE WHEN d.LeaveDate='' THEN NULL WHEN length(d.LeaveDate) != 10 THEN NULL ELSE STR_TO_DATE(d.LeaveDate,'%Y-%m-%d') END,\n" +
            "CASE WHEN d.ProbationPeriod='' THEN NULL ELSE d.ProbationPeriod END,\n" +
            "CASE WHEN d.ApplyReportDate='' THEN NULL ELSE STR_TO_DATE(SUBSTR(d.ApplyReportDate,1,INSTR(d.ApplyReportDate,' ')-1),'%Y-%m-%d') END,\n" +
            "CASE WHEN d.reports_date='' THEN NULL ELSE STR_TO_DATE(SUBSTR(d.reports_date,1,INSTR(d.reports_date,' ')-1),'%Y-%m-%d') END,\n" +
            "CASE WHEN d.Send_Date='' THEN NULL ELSE STR_TO_DATE(SUBSTR(d.Send_Date,1,INSTR(d.Send_Date,' ')-1),'%Y-%m-%d') END,\n" +
            "CASE WHEN d.HringDate='' THEN NULL ELSE STR_TO_DATE(SUBSTR(d.HringDate,1,INSTR(d.HringDate,' ')-1),'%Y-%m-%d') END,\n" +
            "CASE WHEN LENGTH(d.CARD_ID)>4 THEN CONCAT('H3C',RIGHT(d.CARD_ID,4)) ELSE NULL END, d.OfferID, \n" +
            "CASE WHEN d.State='0' THEN 3 \n" +
            "WHEN d.State='1' THEN 5 \n" +
            "WHEN d.State='2' THEN 6 \n" +
            "WHEN d.State='3' THEN 4 \n" +
            "WHEN d.State='4' THEN 11 \n" +
            "WHEN d.State='5' THEN 9 \n" +
            "WHEN d.State='6' THEN 10 \n" +
            "WHEN d.State='99' THEN 13 ELSE -1 END, \n" +
            "CASE WHEN d.Send_Person='beisen' THEN 2 ELSE 1 END, " +
            "NOW(),'hrssinit'\n" +
            "FROM (SELECT a.HC_NO, a.EmpCode, a.DomainAccount, a.NAME, a.EmpTel AS telPhone, a.CARD_ID, a.EmpEmail, a.PersonnelRange,\n" +
            "a.Dept1Name, a.Dept2Name, a.Dept3Name, a.Send_Person, a.ProbationPeriod, a.ApplyReportDate, a.reports_date, a.Send_Date,\n" +
            "a.HringDate, a.State, b.* FROM Offer_Send_Detail a INNER JOIN Offer_EmployeeInfo b ON a.ID = b.OfferID ) d\n" +
            "LEFT JOIN Offer_SocialSecurityAddress c ON d.OfferID = c.offerid AND d.state != '7'")
    public void initWorkerRecordFromHrss(String str);

    @Select("select worker_id from tb_offer_worker where direct_manager like concat('%',#{workerId},'%')")
    List<String> queryWorkerOfManager(@Param("workerId") String workerId);

    List<Map<String, Object>> countPreGroupByDay(@Param("startTime") Date startTime,
                                                 @Param("endTime") Date endTime,
                                                 @Param("deptId") Set<Integer> deptId);
}

